package fr.mairie.parc.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Locale;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import fr.mairie.parc.domain.ExcelFile;

public class ReadFromExcel {

	private ExcelFile excelfile;
	private ArrayList<ExcelFile> listExlFile;
	private ArrayList<ExcelFile> listExl;

	public void init(String filePath) {
		FileInputStream fs = null;
		try {
			fs = new FileInputStream(new File(filePath));
			listExl=(contentReading(fs));
			System.out.println(listExl.size());
			for (int i = 0; i <listExl.size(); i++){
				System.out.println("je suis la"+i);
				System.out.println(listExl.get(i));
			}
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				fs.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	//Returns the Headings used inside the excel sheet
	public void getHeadingFromXlsFile(Sheet sheet) {
		int columnCount = sheet.getColumns();
		for (int i = 0; i < columnCount; i++) {
			System.out.println(sheet.getCell(i, 0).getContents());
		}
	}

	public ArrayList<ExcelFile> contentReading(InputStream fileInputStream) {
		WorkbookSettings ws = null;
		Workbook workbook = null;
		Sheet s = null;
		Cell rowData[] = null;
		int rowCount = '0';
		int columnCount = '0';
		int totalSheet = 0;

		listExlFile = new ArrayList<ExcelFile>();



		try {
			ws = new WorkbookSettings();
			ws.setLocale(new Locale("fr", "FR"));
			workbook = Workbook.getWorkbook(fileInputStream, ws);

			totalSheet = workbook.getNumberOfSheets();
			if(totalSheet > 0) {
				System.out.println("Total Sheet Found:" + totalSheet);
				for(int j=0;j<totalSheet ;j++) {
					System.out.println("Sheet Name:" + workbook.getSheet(j).getName());
				}
			}

			//Getting Default Sheet i.e. 0
			s = workbook.getSheet(0);

			//Reading Individual Cell
			getHeadingFromXlsFile(s);

			//Total Total No Of Rows in Sheet, will return you no of rows that are occupied with some data
			System.out.println("Total Rows inside Sheet:" + s.getRows());
			rowCount = s.getRows();

			//Total Total No Of Columns in Sheet
			System.out.println("Total Column inside Sheet:" + s.getColumns());
			columnCount = s.getColumns();

			//Reading Individual Row Content
			for (int i = 0; i < rowCount; i++) {
				//Get Individual Row
				rowData = s.getRow(i);
				excelfile = new ExcelFile();
				if (rowData[0].getContents().length() != 0) { // the first date column must not null
					for (int j = 0; j < columnCount; j++) {
						switch (j) {
							case 0:
								System.out.println("NumPar:" + rowData[j].getContents());
								excelfile.setNumParc(rowData[j].getContents());
								break;
							case 1:
								System.out.println("Marque:" + rowData[j].getContents());
								excelfile.setMarque(rowData[j].getContents());
								break;
							case 2:
								System.out.println("Model:" + rowData[j].getContents());
								excelfile.setModele(rowData[j].getContents());
								break;
							case 3:
								System.out.println("Date:" + rowData[j].getContents());
								excelfile.setDate(rowData[j].getContents());
								break;
							case 4:
								System.out.println("Batiment:" + rowData[j].getContents());
								excelfile.setBatiment(rowData[j].getContents());
								break;
							case 5:
								System.out.println("Etage:" + rowData[j].getContents());
								excelfile.setEtage(rowData[j].getContents());
								break;
							case 6:
								System.out.println("Salle:" + rowData[j].getContents());
								excelfile.setSalle(rowData[j].getContents());
								break;
							case 7:
								System.out.println("Direction:" + rowData[j].getContents());
								excelfile.setDirection(rowData[j].getContents());
								break;
							case 8:
								System.out.println("Service:" + rowData[j].getContents());
								excelfile.setService(rowData[j].getContents());
								break;
							case 9:
								System.out.println("Utilisateur:" + rowData[j].getContents());
								excelfile.setUtilisateur(rowData[j].getContents());
								break;
							default:break;
						}
					}
					listExlFile.add(excelfile);
				}

			}

			workbook.close();

		} catch (IOException e) {
			e.printStackTrace();
		} catch (BiffException e) {
			e.printStackTrace();
		}
		return listExlFile;
	}

		public static void main(String[] args) {
		try {
			ReadFromExcel xlReader = new ReadFromExcel();
			xlReader.init("C:/Apps/oo.xls");

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}